package com.sample.javabean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;




import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.sample.action.form.TeacherForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;


/**
 * 教师信息类
 * @author梁桂花
 *
 * 2018-10-20
 */
public class Teacher {
	
	private static Log LOG =LogFactory.getLog(Teacher.class);
	//定义属性
	private String	name;//姓名
	private String	sex	;//	性别
	private String	cardno	;//	身份证号
	private String	address	;//	家庭住址
	private String	className	;//	身份证号
	private String	tel	;//	联系方式
	
	private JdbcUtils jdbcUtils ;

	public Teacher(){
		jdbcUtils=new JdbcUtils();
	}
	
	/**
	 * 教师信息列表
	 * @return
	 */
	public List<Teacher> findAll(){
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Teacher> teacherList =new ArrayList<Teacher>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			//构造插入sql
			String sql="select * from t_teacher where 1=1";
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				
				String name =rs.getString("name");
			    String	sex=rs.getString("sex");
			    String cardno=rs.getString("cardno");
			    String	address=rs.getString("address");
			    String className=rs.getString("className");
				String	tel=rs.getString("tel");
				 Teacher model=new Teacher( name,sex,cardno,address,className,tel);
				
				teacherList.add(model);
							}
			
		}catch (Exception e) {
			e.printStackTrace();
			LOG.error("加载教师列表失败!", e);
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return teacherList;
		
	}
	
     public void load()throws Exception{
 		Connection conn = null;
 		Statement stmt = null;
 		ResultSet rs =null; 
 		try {
 			conn = jdbcUtils.getConn();
 			stmt = conn.createStatement();
 			
 			String sql = "select * from  t_teacher where cardno='"+this.cardno+"'";
 			// 4:鎵ч敓鏂ゆ嫹SQL閿熸枻鎷烽敓鏂ゆ嫹閿熸埅鏂ゆ嫹閿燂拷
 			rs=stmt.executeQuery(sql);
 			if(rs.next()){
 				
 				this.name =rs.getString("name");
 				this.sex=rs.getString("sex");
 				this.cardno =rs.getString("cardno");
 			   	this.address=rs.getString("address");
 			   this.className =rs.getString("className");
 				this.tel=rs.getString("tel");
 				
 				
 			}
 			// 5:閿熸埅鎲嬫嫹閿熸枻鎷锋簮
 		} catch (SQLException e) {
 			e.printStackTrace();
 			throw new SQLException("加载教师信息");
 		} finally {
 			jdbcUtils.close(conn, stmt);
 		}	
 	}
 	public void editTeacher() {

 		Connection conn = null;
 		Statement stmt = null;
 		try {
 			conn = jdbcUtils.getConn();
 			stmt = conn.createStatement();
 			
 			String sql = "update  t_teacher set name='"+this.name+"',sex='"+this.sex+"',cardno='"+this.cardno+"',address='"+this.address+"',className='"+this.className+"',tel='"+this.tel+"' where cardno='"+this.cardno+"'";
 			
 			stmt.executeUpdate(sql);
 			
 		} catch (SQLException e) {
 			e.printStackTrace();
 		} finally {
 			jdbcUtils.close(conn, stmt);
 		}

 	}
 	public void delTeacher() {

 		Connection conn = null;
 		Statement stmt = null;
 		try {
 			conn = jdbcUtils.getConn();
 			stmt = conn.createStatement();
 			
 			String sql = "delete from t_teacher  where cardno='"+this.cardno+"'";
 			
 			System.out.println("删除教师信息");
 			stmt.executeUpdate(sql);
 			
 		} catch (SQLException e) {
 			e.printStackTrace();
 		} finally {
 			jdbcUtils.close(conn, stmt);
 		}

 	}
 	public void add(){
 		Connection conn =null;
 		Statement stmt=null;
 		try{
 			
 			 conn =jdbcUtils.getConn();
 		
 			String sql="insert into t_teacher(name,sex,cardno,address,className,tel) values('"+this.name+"','"+this.sex+"','"+this.cardno+"','"+this.address+"','"+this.className+"','"+this.tel+"')";
 			
 			 stmt =conn.createStatement();
 			stmt.executeUpdate(sql);
 		}catch (Exception e) {
 			e.printStackTrace();
 			
 		}finally{
 			jdbcUtils.close(conn, stmt);
 			
 		}
 		
 	}
 	public Pager<Teacher> list(int page,int size,TeacherForm form) {

 		Connection conn = null;
 		Statement stmt = null;
 		ResultSet rs =null;
 		Pager<Teacher> results=new Pager<Teacher>();
 		results.setPage(page);
 		results.setSize(size);
 		try {
 			conn = jdbcUtils.getConn();
 			stmt = conn.createStatement();
 			int start=(page-1)*size;
 			int limit=size;
 			
 			String selectSql="select * from t_teacher where 1=1";
 			if(form!=null&&form.getName()!=null&&!"".equals(form.getName())){
 				selectSql +=" and name like'%"+form.getName()+"%'";
 			}
 			
 			
 			String countSql ="select count(*)as c from ("+selectSql+") as t1";
 			
 			 String pageSql=selectSql+" limit "+start+","+limit;
 			 rs= stmt.executeQuery(countSql);
 			 int total=0;
 			 while(rs.next()){
 				 total=rs.getInt("c");
 			 }
 			 results.setTotal(total);
 			 
 			 rs= stmt.executeQuery(pageSql);
 			 while(rs.next()){
 			 
 				String name =rs.getString("name");
			    String	sex=rs.getString("sex");
			    String cardno=rs.getString("cardno");
			    String	address=rs.getString("address");
			    String className=rs.getString("className");
				String	tel=rs.getString("tel");
 				
 				 Teacher model=new Teacher();
 				  
 				  model.setName(name);
 				  model.setSex(sex);
 				 model.setCardno(cardno);
 				  model.setAddress(address);
 				 model.setClassName(className);
 				  model.setTel(tel);
 				
 				  
 		
 				  results.getDataSets().add(model);
 			 }
 			 results.init();
 			
 		} catch (SQLException e) {
 			e.printStackTrace();
 		} finally {
 			jdbcUtils.close(conn, stmt);
 		}
 		return results;

 	}
 	

	public Teacher( String name, String sex,String cardno,
			String address,String className,String tel ) {
		super();
		
		
		this.name = name;
		this.sex=sex;
		this.cardno=cardno;
		this.address=address;
		this.className = className;
	    this.tel=tel;
		
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getCardno() {
		return cardno;
	}

	public void setCardno(String cardno) {
		this.cardno = cardno;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getClassName() {
		return className;
	}

	public void setClassName(String className) {
		this.className = className;
	}

	public String getTel() {
		return tel;
	}

	public void setTel(String tel) {
		this.tel = tel;
	}

	public JdbcUtils getJdbcUtils() {
		return jdbcUtils;
	}

	public void setJdbcUtils(JdbcUtils jdbcUtils) {
		this.jdbcUtils = jdbcUtils;
	}

	
	
	
}
